﻿using DBUtil.MetaData;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBUtil.Provider.MySql;

public partial class MySqlManage : DBManage
{
    #region 生成insert语句    
    #region 生成insert语句
    /// <inheritdoc />
    public override async Task<(long exportCount, string sql, long totalCount)> GenerateInsertSqlAsync(string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var objName = db.ParseQuotedName(tableQuotedName);
        var sb = new StringBuilder();
        var exportCount = await GenerateInsertSqlInternal(str =>
         {
             sb.Append(str);
             return ValueTask.CompletedTask;
         }, objName, limitCount, filter, order);
        var totalCount = await GetCountAsync(objName.FullNameQuoted, filter);
        var sql = sb.ToString();
        sb.Clear();
        return (exportCount, sql, totalCount);
    }

    /// <inheritdoc />
    public override async Task<long> GenerateInsertSqlFileAsync(string fileAbsPath, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        var dir = Path.GetDirectoryName(fileAbsPath);
        if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);

        using var fs = new FileStream(fileAbsPath, FileMode.CreateNew);
        return await GenerateInsertSqlAsync(fs, tableQuotedName, limitCount, filter, order);
    }

    /// <inheritdoc />
    public override async Task<long> GenerateInsertSqlAsync(Stream stream, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var objName = db.ParseQuotedName(tableQuotedName);
        var count = await GenerateInsertSqlInternal(async str =>
        {
            var bs = Encoding.UTF8.GetBytes(str);
            await stream.WriteAsync(bs);
        }, objName, limitCount, filter, order);
        return count;
    }

    private async Task<long> GenerateInsertSqlInternal(Func<string, ValueTask> func, TableName objName, int limitCount = 0, string filter = null, string order = null)
    {
        var table = await ShowTableDetailAsync(objName.Name, objName.SchemaName);
        var colNames = table.Columns.Select(col => db.AddQuote(col.Name)).ToList();
        await func($"-- 开始执行insert语句\r\n");
        await func($"insert into {objName.FullNameQuoted}({string.Join(",", colNames)}) values");
        var count = 0;
        await db.SelectDataReaderAsync(async reader =>
        {
            var index = 0;
            var rowSb = new StringBuilder();
            while (await reader.RawReader.ReadAsync())
            {
                if (index > 0)
                {
                    if (index == 1)
                    {
                        //当前第二行, 把第一行的输出
                        await func("\r\n    ");
                        await func(rowSb.ToString());
                        await func(",");
                    }
                    else
                    {
                        //当前是第三行...将上一行的输出
                        await func("\r\n    ,");
                        await func(rowSb.ToString());
                        await func(",");
                    }
                    rowSb.Clear();
                }
                rowSb.Append('(');
                index++;
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    var col = table.Columns[i];
                    var value = reader.RawReader[col.Name];
                    if (i > 0) rowSb.Append(',');
                    if (value == DBNull.Value)
                    {
                        rowSb.Append("null");
                    }
                    else if (value is string str)
                    {
                        rowSb.Append("'" + db.EscapeString(str) + "'");
                    }
                    else if (value is byte[] bs)
                    {
                        rowSb.Append("0x").Append(bs.ToHex());
                    }
                    else if (value is DateTime || value is DateTimeOffset || value is TimeSpan || value is MySqlConnector.MySqlDateTime)
                    {
                        rowSb.Append(db.ConvertToSqlSeg(value));
                    }
                    else if (value is Guid)
                    {
                        rowSb.Append("'" + value.ToString() + "'");
                    }
                    else if (value is bool b)
                    {
                        rowSb.Append(b ? "1" : "0");
                    }
                    else
                    {
                        rowSb.Append(db.EscapeString(value.ToString()));
                    }
                }
                rowSb.Append(')');
                count++;
            }
            ;
            if (index == 0)
            {
                //一行也没有
                await func("no data");
            }
            else if (index == 1)
            {
                //只有一行, 收尾
                await func(rowSb.ToString());
                await func(";");
            }
            else
            {
                //有很多行, 收尾
                await func("\r\n    ");
                await func(rowSb.ToString());
                await func(";");
            }
            rowSb.Clear();
        }, $"select * from {objName.FullNameQuoted} {db.GenerateWhereClause(filter)} {order} {(limitCount > 0 ? $"limit {limitCount}" : "")}");

        await func($"\r\n-- {objName.FullNameQuoted}: 共执行了 {count} 次insert(自行判断执行成功或失败)'");
        var total = await GetCountAsync(objName.FullNameQuoted, filter);
        return total;
    }
    #endregion

    #region 生成建表脚本
    /// <inheritdoc />
    public override async Task<string> GenerateCreateTableSqlAsync(string tablePureName, string schemaPureName = null)
    {
        var fullQuoted = schemaPureName.IsNotNullOrWhiteSpace() ? $"{db.AddQuote(schemaPureName)}.{db.AddQuote(tablePureName)}" : db.AddQuote(tablePureName);
        var dt = await db.SelectDataTableAsync($"show create table {fullQuoted}");
        if (dt.Rows.Count < 1) return "";
        return
            $"""
            -- drop table if exists {fullQuoted};
            {dt.Rows[0][1]};
            """;
    }

    /// <inheritdoc />
    public override async Task GenerateCreateTableSqlBatchAsync(Stream stream, IEnumerable<string> tableQuotedNames, bool includeInsertSql = false)
    {
        //生成脚本
        var counter = 0;
        await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 开始建表: 共 {counter} 个---------------------\r\n"));
        foreach (var table in tableQuotedNames)
        {
            counter++;
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- start 表: {table}\r\n"));
            await stream.WriteAsync(Encoding.UTF8.GetBytes(await GenerateCreateTableSqlAsync(table)));
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- end 表: {table}\r\n"));
        }
        await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 建表结束, 共: {counter} 个---------------------\r\n"));

        //插入数据
        if (includeInsertSql)
        {
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 开始插入数据---------------------\r\n"));
            foreach (var table in tableQuotedNames)
            {
                await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- start 表: {table}\r\n"));
                await GenerateInsertSqlAsync(stream, table);
                await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- end 表: {table}\r\n"));
            }
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 插入结束---------------------\r\n"));
        }
    }
    #endregion

    /// <inheritdoc />
    public async Task<string> GenerateCreateProcSqlAsync(string procQuotedName)
    {
        var objName = db.ParseQuotedName(procQuotedName);
        var sql = $"show create procedure {objName.FullNameQuoted}";
        var dt = await db.SelectDataTableAsync(sql);
        if (dt.Rows.Count < 1) return "";
        return
            $"""
            -- drop procedure if exists {objName.FullNameQuoted};
            {dt.Rows[0]["Create Procedure"]};
            """;
    }

    #region GenerateCodeAsync
    /// <inheritdoc />
    public override async Task<string> GenerateCodeAsync(string tablePureName, string schemaPureName)
    {
        return await _generateCode(tablePureName);
    }

    private async Task<string> _generateCode(string tablePureName, string schemaPureName = null, bool isView = false)
    {
        MySqlTable table = null;
        MySqlView view = null;
        if (isView)
        {
            view = await ShowViewDetailAsync(tablePureName, schemaPureName) as MySqlView;
        }
        else
        {
            table = await ShowTableDetailAsync(tablePureName, schemaPureName) as MySqlTable;
        }
        var name = (isView ? view.Name : table.Name);
        var columns = isView ? view.Columns : table.Columns;

        var extEnum = "";
        var getColumnProp = (Column column) =>
        {
            var col = column as MySqlColumn;
            var type = "";
            switch (col.Type)
            {
                case EnumMySqlColumnType.Bit:
                    if (col.NUMERIC_PRECISION == 1) type = "bool";
                    if (col.NUMERIC_PRECISION == 8) type = "sbyte";
                    if (col.NUMERIC_PRECISION == 16) type = "ushort";
                    if (col.NUMERIC_PRECISION == 32) type = "uint";
                    if (col.NUMERIC_PRECISION == 64) type = "ulong";
                    type = "ulong";
                    break;

                case EnumMySqlColumnType.TinyInt: type = "byte"; break;
                case EnumMySqlColumnType.TinyIntUnSigned: type = "sbyte"; break;
                case EnumMySqlColumnType.SmallInt: type = "short"; break;
                case EnumMySqlColumnType.SmallIntUnSigned: type = "ushort"; break;
                case EnumMySqlColumnType.MediumInt: type = "int"; break;
                case EnumMySqlColumnType.MediumIntUnSigned: type = "uint"; break;
                case EnumMySqlColumnType.Int: type = "int"; break;
                case EnumMySqlColumnType.IntUnSigned: type = "uint"; break;
                case EnumMySqlColumnType.BigInt: type = "long"; break;
                case EnumMySqlColumnType.BigIntUnSigned: type = "ulong"; break;

                case EnumMySqlColumnType.Float: type = "double"; break;
                case EnumMySqlColumnType.Double: type = "double"; break;
                case EnumMySqlColumnType.Decimal: type = "decimal"; break;

                case EnumMySqlColumnType.Char:
                case EnumMySqlColumnType.VarChar:
                case EnumMySqlColumnType.TinyText:
                case EnumMySqlColumnType.Text:
                case EnumMySqlColumnType.MediumText:
                case EnumMySqlColumnType.LongText:
                    type = "string";
                    break;

                case EnumMySqlColumnType.Binary:
                case EnumMySqlColumnType.VarBinary:
                case EnumMySqlColumnType.TinyBlob:
                case EnumMySqlColumnType.Blob:
                case EnumMySqlColumnType.MediumBlob:
                case EnumMySqlColumnType.LongBlob:
                    type = "byte[]";
                    break;

                case EnumMySqlColumnType.Year: type = "int"; break;
                case EnumMySqlColumnType.Date: //.net6以上使用dateonly?
                case EnumMySqlColumnType.Time: //.net6以上使用timeonly?
                case EnumMySqlColumnType.DateTime:
                case EnumMySqlColumnType.TimeStamp:
                    type = "DateTime";
                    break;

                case EnumMySqlColumnType.Enum:
                    type = $"Enum{col.Name}";
                    extEnum += generateEnum(col.TypeString, col.Name);
                    break;
                case EnumMySqlColumnType.Set:
                    type = $"Enum{col.Name}";
                    extEnum += generateEnum(col.TypeString, col.Name, true);
                    break;

                case EnumMySqlColumnType.Geometry:
                case EnumMySqlColumnType.Point:
                case EnumMySqlColumnType.LineString:
                case EnumMySqlColumnType.Polygon:
                case EnumMySqlColumnType.GeometryCollection:
                case EnumMySqlColumnType.MultiPoint:
                case EnumMySqlColumnType.MultiLineString:
                case EnumMySqlColumnType.MultiPolygon:
                    type = "byte[]";
                    break;

                default:
                    break;
            }
            if (col.IsNullAble)
            {
                if (new string[] { "object", "byte[]", "string" }.Contains(type)) { }
                else
                {
                    type += "?";
                }
            }
            var desc = col.Desc;
            desc = desc.Replace("\r\n", " ");
            return $$"""
        /// <summary>
        /// {{desc}}
        /// </summary>
        [Column("{{col.Name.Replace("\"", "\\\"")}}]")]
        public {{type}} {{formmaterName(col.Name)}} { get; set; }";
        """;
        };

        return $$"""
using System;
using System.ComponentModel.DataAnnotations.Schema;

[Table("{{db.AddQuote(name)}}")]
public class {{formmaterName(name)}}
{
{{columns.Select(i => getColumnProp(i))}}
}

{{extEnum}}
""";
    }
    private string generateEnum(string typestring, string colname, bool flags = false)
    {
        if (typestring.IsNullOrWhiteSpace()) return "";
        var code = $$"""
public enum Enum{{formmaterName(colname)}}
{

""";
        if (flags)
        {
            code = $@"
[Flags]
public enum Enum{formmaterName(colname)}
{{
";
        }
        typestring = typestring.Substring(flags ? 3 : 4);
        typestring = typestring.Trim('(', ')');
        var arr = typestring.SplitAndTrimTo<string>(",").Select(i => i.Trim('\'')).ToList();
        var tmp = "";
        for (var i = 0; i < arr.Count; i++)
        {
            tmp += $"\t{arr[i]} = {(flags ? Math.Pow(2, i) : (i + 1))},\r\n";
            if (i == arr.Count - 1) tmp = tmp.TrimEnd('\r', '\n', ',');
        }
        code += tmp;
        code += @"
}";
        return code;
    }
    private string formmaterName(string name)
    {
        if (name.IsNullOrWhiteSpace()) return name;
        var segs = name.SplitAndTrimTo<string>("_");
        if (segs.Count == 1) return segs[0].ToUpperFirstLetter();
        var res = "";
        for (var i = 0; i < segs.Count; i++)
        {
            res += segs[i].ToUpperFirstLetter();
        }
        return res;
    }
    #endregion
    #endregion
}
public static class StringExtensions
{
    /// <summary>
    /// 首字母大写
    /// </summary>
    public static string ToUpperFirstLetter(this string str)
    {
        if (str.IsNullOrWhiteSpace()) return str;
        if (str.Length == 1) return str.ToUpper();
        return (str.FirstOrDefault() + "").ToUpper() + str.Skip(1).Take(str.Length - 1).ToStringSeparated("");
    }
}
